package com.bluesky.light.healthnews;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.bluesky.light.util.DB;

public class HealthNewsDAO {

	/**
	 * 從數據庫中得到最近的新聞
	 * 
	 * @return
	 */
	public HealthNews getLastNews() {
		Connection conn = DB.getConn();
		String sql = "select * from t_healthnews order by updatetime desc";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		HealthNews lastNews = null;

		try {
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				lastNews = new HealthNews();
				lastNews.setTitle(rs.getString("title"));
				lastNews.setContent(rs.getString("content"));
				lastNews.setUrl(rs.getString("url"));
				lastNews.setUpdateTime(rs.getTimestamp("updatetime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(rs);
		}

		return lastNews;

	}

	/**
	 * 將新聞插入到數據庫中
	 * 
	 * @param news
	 */
	public void add(HealthNews news) {
		Connection conn = DB.getConn();
		String sql = "insert into t_healthnews (title, content, url, updatetime) values (?, ?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql);

		try {
			pstmt.setString(1, news.getTitle());
			pstmt.setString(2, news.getContent());
			pstmt.setString(3, news.getUrl());
			pstmt.setTimestamp(4, new Timestamp(news.getUpdateTime().getTime()));
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}

	}

	/**
	 * 在數據庫中刪除所有新聞
	 */
	public void delete() {
		Connection conn = DB.getConn();
		String sql = "delete from t_healthnews";
		Statement stmt = DB.getStatement(conn);

		try {
			DB.excuteUpdate(stmt, sql);
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}

	/**
	 * 在數據庫查找所有新聞, 根據日期的先後次序排列
	 * 
	 * @return
	 */
	public List<HealthNews> getNewses() {
		Connection conn = DB.getConn();
		String sql = "select * from t_healthnews order by updatetime desc";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		List<HealthNews> newses = new ArrayList<HealthNews>();

		try {
			rs = DB.getResultSet(stmt, sql);
			while (rs.next()) {
				HealthNews news = new HealthNews();
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setUrl(rs.getString("url"));
				news.setUpdateTime(rs.getTimestamp("updatetime"));
				newses.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(rs);
		}

		return newses;
	}
}
